﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;

namespace seo_tool.lib
{
    class Sqlite
    {
        SQLiteConnection conn;

        public Sqlite()
        {

        }

        public Sqlite(string filename = "")
        {
            connect(filename);
        }

        public void createDatabase(string filename)
        {
            SQLiteConnection.CreateFile(filename);
        }

        public void connect(string filename)
        {
            conn = new SQLiteConnection($"Data Source={filename};Version=3;");
            conn.Open();
        }

        public void createTable(string sql)
        {
            //sql = "create table highscores (name varchar(20), score int)";
            SQLiteCommand command = new SQLiteCommand(sql, conn);
            command.ExecuteNonQuery();
        }

        public void insert(string sql)
        {
            //sql = "insert into highscores (name, score) values ('Me', 3000)";
            SQLiteCommand command = new SQLiteCommand(sql, conn);
            command.ExecuteNonQuery();
        }

        public int insertGetId(string sql)
        {
            //sql = "insert into highscores (name, score) values ('Me', 3000)";
            SQLiteCommand command = new SQLiteCommand(sql, conn);
            object objValue = command.ExecuteScalar();
            int insertId = Convert.ToInt32(objValue);
            return insertId;
        }

        public void update(string sql)
        {
            //sql = "UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6";
            SQLiteCommand command = new SQLiteCommand(sql, conn);
            command.ExecuteNonQuery();
        }

        public SQLiteDataReader select(string tableName)
        {
            //sql = "select * from highscores order by score desc";
            SQLiteCommand command = new SQLiteCommand($"select * from {tableName} order by id asc", conn);
            SQLiteDataReader reader = command.ExecuteReader();
            return reader;
            //while (reader.Read()) Console.WriteLine("Name: " + reader["name"] + "\tScore: " + reader["score"]);
        }

        public string findConfig(string key)
        {
            SQLiteCommand command = new SQLiteCommand($"select * from config where key = '{key}' limit 1", conn);
            SQLiteDataReader reader = command.ExecuteReader();
            reader.Read();
            string value = (string)reader["value"];
            reader.Close();
            return value;
        }

        public void clear(string tableName)
        {
            SQLiteCommand command = new SQLiteCommand($"delete from {tableName}", conn);
            command.ExecuteNonQuery();
            command = new SQLiteCommand($"update sqlite_sequence set seq = 0 where name = '{tableName}'", conn);
            command.ExecuteNonQuery();
        }

        public void resetShoulu()
        {
            SQLiteCommand command = new SQLiteCommand("update shoulu set is_shoulu = '', suoyin = 0, is_run = '', search = ''", conn);
            command.ExecuteNonQuery();
        }

        public void resetXiangguan()
        {
            SQLiteCommand command = new SQLiteCommand("delete from xiangguan where level != 0", conn);
            command.ExecuteNonQuery();
            command = new SQLiteCommand("update xiangguan set xiala = '', xiangguan = '', is_run = '', search = ''", conn);
            command.ExecuteNonQuery();
        }

        public SQLiteDataReader getXiangguan(int level)
        {
            //sql = "select * from highscores order by score desc";
            SQLiteCommand command = new SQLiteCommand($"select * from xiangguan where level = {level} and xiala != '' order by id asc", conn);
            SQLiteDataReader reader = command.ExecuteReader();
            return reader;
            //while (reader.Read()) Console.WriteLine("Name: " + reader["name"] + "\tScore: " + reader["score"]);
        }

        public SQLiteDataReader selectRun(string tableName, int limit = 50)
        {
            //sql = "select * from highscores order by score desc";
            SQLiteCommand command = new SQLiteCommand($"select * from {tableName} where is_run = '' order by id asc limit {limit}", conn);
            SQLiteDataReader reader = command.ExecuteReader();
            return reader;
            //while (reader.Read()) Console.WriteLine("Name: " + reader["name"] + "\tScore: " + reader["score"]);
        }
    }
}
